In [128]:
import requests
import pandas as pd
import io
import tweepy
import json
import sqlite3
import seaborn as sns
import matplotlib.pyplot as plt
from matplotlib.dates import DateFormatter
import matplotlib.dates as dates
from IPython.core.display import Image, display
import urllib.request
1.1 WeRateDogs archive¶
Gather data from local csv file.
In [2]:
twitter_archive_df = pd.read_csv('../data/twitter-archive-enhanced.csv')
In [3]:
twitter_archive_df.head(20)
Out[3]:
In [4]:
twitter_archive_df.shape
Out[4]:
1.2 Dog breed prediction¶
Gather data from a URL with the requests library.
In [5]:
r = requests.get('https://d17h27t6h515a5.cloudfront.net/topher/2017/August/599fd2ad_image-predictions/image-predictions.tsv')
In [6]:
r.status_code
Out[6]:
In [7]:
r.headers['content-type']
Out[7]:
In [8]:
raw_data = r.content
In [9]:
prediction_df = pd.read_csv(io.StringIO(raw_data.decode('utf-8')), sep='\t')
prediction_df.tail(2)
Out[9]:
In [157]:
# write tsv
prediction_df.to_csv('../data/image-predictions.tsv', sep='\t')
1.3 Twitter like and retweet counts¶
Gather data via a twitter API. Key and tokens are removed.
In [12]:
consumer_key = 'XXXXXX'
consumer_secret = 'XXXXXX'
access_token ='XXXXXX'
access_token_secret = 'XXXXXX'
auth = tweepy.OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_token, access_token_secret)
api = tweepy.API(auth, wait_on_rate_limit=True, wait_on_rate_limit_notify=True)
In [13]:
tweets = []
for tweet_id in list(twitter_archive_df['tweet_id']):
try:
tweet = api.get_status(tweet_id) #, tweet_mode='extended')
tweets.append(tweet)
except tweepy.TweepError as e:
print(e.response.text)
In [150]:
len(tweets)
Out[150]:
In [151]:
# extract json content of every tweet into a list
my_list_of_dicts = []
for each_json_tweet in tweets:
my_list_of_dicts.append(each_json_tweet._json)
In [152]:
# write every tweet's json into a txt file
with open('../data/tweet_json.txt', 'w') as file:
file.write(json.dumps(my_list_of_dicts, indent=4))
In [153]:
# extract the needed variables and convert it to a pandas dataframe
tweet_list = []
with open('../data/tweet_json.txt', encoding='utf-8') as json_file:
all_data = json.load(json_file)
for each_dictionary in all_data:
tweet_id = each_dictionary['id']
text = each_dictionary['text']
favorite_count = each_dictionary['favorite_count']
retweet_count = each_dictionary['retweet_count']
created_at = each_dictionary['created_at']
tweet_list.append({'tweet_id': str(tweet_id),
'text': str(text),
'favorite_count': int(favorite_count),
'retweet_count': int(retweet_count),
'created_at': created_at,
})
tweet_json = pd.DataFrame(tweet_list, columns =
['tweet_id', 'text',
'favorite_count', 'retweet_count',
'created_at'])
In [154]:
tweet_json.shape
Out[154]:
In [248]:
tweet_json.head()
Out[248]:
In [21]:
# write dataframe to a csv file
tweet_json.to_csv('../data/tweet_dogs.csv', index=False)
In [22]:
# read tweet_dogs.csv (to avoid accessing the API every time)
tweets_df = pd.read_csv('../data/tweet_dogs.csv')
tweets_df.head(20)
Out[22]:
2. Tidying + Cleaning¶
Cleaning¶
- convert timestamp and created_at columns to datetime
- convert doggo, floofer, pupper and puppo columns to bool
- remove retreats (tweets without images)
- reomving tweets without dog name
- remove tweets with retweet id
- remove rows where rating_denominator != 10
- remove when p1_dog (and p2 ?) is False in third df
- remove newer than August 1st, 2017.
Tidying¶
- inner join on tweet_id (keep only tweets where data is available in all 3 dfs)
- take ranking from first df
- take name in from first df
- extract image from second df
- take retreat count from third df
- take favorite count from third df
- take jpg_url from second df
- take all 3 predictions and confidence values from third df
- remove needless columns
2.1 Cleaning¶
The cleaning step is chosen first, to see more dirty data in single dataframes before joining all three dataframes. Usually Tidying would be the first step.
In [23]:
# copy every dataframe
twitter_archive_df_cleaned = twitter_archive_df.copy()
prediction_df_cleaned = prediction_df.copy()
tweets_df_cleaned = tweets_df.copy()
Get info about data types of every dataframe.
In [24]:
twitter_archive_df_cleaned.info()
In [25]:
prediction_df_cleaned.info()
In [26]:
tweets_df_cleaned.info()
Check if every tweet id is unique and there are no duplicates.
In [249]:
twitter_archive_df_cleaned['tweet_id'].nunique()
twitter_archive_df_cleaned['tweet_id'].duplicated().any()
Out[249]:
Convert to datetime:
In [28]:
# convert date columns to datetime
twitter_archive_df_cleaned['timestamp'] = pd.to_datetime(twitter_archive_df_cleaned['timestamp'])
tweets_df_cleaned['created_at'] = pd.to_datetime(tweets_df_cleaned['created_at'])
print(type(twitter_archive_df_cleaned['timestamp'][0]), type(tweets_df_cleaned['created_at'][0]))
Make data type of doggo, floofer, pupper and puppo columns bool values:
In [29]:
# doggo, floofer, pupper and puppo columns to bool
#twitter_archive_df_cleaned['puppo'].unique() # to explore
twitter_archive_df_cleaned['doggo'].replace({'doggo': True, 'None': False}, inplace=True)
twitter_archive_df_cleaned['floofer'].replace({'floofer': True, 'None': False}, inplace=True)
twitter_archive_df_cleaned['pupper'].replace({'pupper': True, 'None': False}, inplace=True)
twitter_archive_df_cleaned['puppo'].replace({'puppo': True, 'None': False}, inplace=True)
print(twitter_archive_df_cleaned['doggo'].unique()) # to test
print(twitter_archive_df_cleaned['floofer'].unique()) # to test
print(twitter_archive_df_cleaned['pupper'].unique()) # to test
print(twitter_archive_df_cleaned['puppo'].unique()) # to test
Tweets without image url are no original rating. Remove them:
In [30]:
# tweets without image url are no original rating
#print(twitter_archive_df[twitter_archive_df['expanded_urls'].isnull()]) # for exploration
rows_before = twitter_archive_df_cleaned.shape[0]
twitter_archive_df_cleaned = twitter_archive_df_cleaned.dropna(axis=0, subset=['expanded_urls'])
print("Dropped ", rows_before-twitter_archive_df_cleaned.shape[0], " due to non-original rating tweets.")
Rating tweets without dog name are not wanted for this analysis later. Remove them:
In [31]:
# rating tweets without dog name
#twitter_archive_df[twitter_archive_df['name'] == "None"].shape[0] # to explore
rows_before = twitter_archive_df_cleaned.shape[0]
twitter_archive_df_cleaned = twitter_archive_df_cleaned[twitter_archive_df_cleaned['name'] != "None"]
print("Dropped ", rows_before-twitter_archive_df_cleaned.shape[0], " due to unknown dog name.")
Remove tweets with retweeted_status_id, because they are retweet-tweets and no original rating tweets.
In [32]:
# remove tweets with retweeted_status_id
#twitter_archive_df[~twitter_archive_df['retweeted_status_id'].isnull()] # for exploration
rows_before = twitter_archive_df_cleaned.shape[0]
twitter_archive_df_cleaned = twitter_archive_df_cleaned[twitter_archive_df_cleaned['retweeted_status_id'].isnull()]
print("Dropped ", rows_before-twitter_archive_df_cleaned.shape[0], " due to re-tweets.")
If the denominator is not 10, then it is no valid rating. This rows will be removed as well:
In [33]:
# no valid rating
#twitter_archive_df_cleaned[twitter_archive_df_cleaned.rating_denominator!= 10] # for exploration
rows_before = twitter_archive_df_cleaned.shape[0]
twitter_archive_df_cleaned = twitter_archive_df_cleaned[twitter_archive_df_cleaned.rating_denominator== 10]
print("Dropped ", rows_before-twitter_archive_df_cleaned.shape[0], " due to invalid rating denominator.")
If the neural network can not predict that it is a dog from the image, then it will be removed as well:
In [34]:
#prediction_df_cleaned[prediction_df.p1_dog == False] # for exploration
rows_before = prediction_df_cleaned.shape[0]
prediction_df_cleaned = prediction_df_cleaned[prediction_df.p1_dog == True]
print("Dropped ", rows_before-prediction_df_cleaned.shape[0], " due to no-dog prediction.")
Remove all tweets after August 1st 2017:
In [35]:
# drop all newer than August 1st 2017
#tweets_df_cleaned[tweets_df_cleaned.created_at > '2017-08-01'] # to explore
rows_before = tweets_df_cleaned.shape[0]
tweets_df_cleaned = tweets_df_cleaned[tweets_df_cleaned.created_at < '2017-08-01']
print("Dropped ", rows_before-tweets_df_cleaned.shape[0], " due to date limit")
rows_before = twitter_archive_df_cleaned.shape[0]
twitter_archive_df_cleaned = twitter_archive_df_cleaned[twitter_archive_df_cleaned.timestamp < '2017-08-01']
print("Dropped ", rows_before-twitter_archive_df_cleaned.shape[0], " due to date limit")
2.2 Tidying¶
In [36]:
print("rows of twitter_archive_df_cleaned: ", twitter_archive_df_cleaned.shape[0])
print("rows of prediction_df_cleaned: ", prediction_df_cleaned.shape[0])
print("rows of tweets_df_cleaned: ", tweets_df_cleaned.shape[0])
In [37]:
print("columns of twitter_archive_df_cleaned: ", twitter_archive_df_cleaned.shape[1])
print("columns of prediction_df_cleaned: ", prediction_df_cleaned.shape[1])
print("columns of tweets_df_cleaned: ", tweets_df_cleaned.shape[1])
Merge all dataframes:
In [38]:
# join twitter_archive_df_cleaned and prediction_df_cleaned
joined_df = twitter_archive_df_cleaned.join(prediction_df_cleaned.set_index('tweet_id'), on='tweet_id', how='inner')
In [39]:
joined_df.shape
Out[39]:
In [40]:
# join also with tweets_df_cleaned
joined_df = joined_df.join(tweets_df_cleaned.set_index('tweet_id'), on='tweet_id', how='inner', lsuffix='', rsuffix='_otweet')
In [41]:
joined_df.shape
Out[41]:
In [42]:
joined_df.head(2)
Out[42]:
Check if the duplicated column for timestamps have the same content:
In [43]:
# check if timestamps are all the same after joining
joined_df[joined_df['timestamp'] == joined_df['created_at']]
Out[43]:
In [44]:
#joined_df[joined_df['jpg_url'] == joined_df['expanded_urls']] # for exploration
Remove all columns that are duplicates or that are not needed for later analysis:
In [45]:
# remove needless columns
joined_df.drop(columns=['created_at', 'in_reply_to_status_id', 'in_reply_to_user_id', 'source', 'retweeted_status_id', 'retweeted_status_user_id', 'retweeted_status_timestamp', 'text_otweet', 'expanded_urls'], inplace=True)
In [46]:
joined_df
Out[46]:
In [47]:
joined_df.columns
Out[47]:
Store cleaned and tidies data¶
In [48]:
joined_df.to_csv('../data/twitter_archive_master.csv', index=False)
In [49]:
conn = sqlite3.connect("../data/twitter_archive_master.db")
joined_df.to_sql("twitter_archive_master", conn, if_exists="replace")
In [50]:
# test
df = pd.read_sql_query("select * from twitter_archive_master;", conn)
df.head()
Out[50]:
Analyze and Visualize¶
Most rated value¶
the most rated values and the highest rated value:
In [51]:
df['rating_numerator'].value_counts()
Out[51]:
Best rated dogs¶
SPOTTED: There is a error in the rate extraction from text, because in the tweet the dog has a rate of 9.75 not 75.
Also the rating 27 is meant as a 11.27 ratings. So they weren't extracted correctly. Now they must be cleaned as well.
In [52]:
#df[df['rating_numerator'] == 27] # test
rows = df.shape[0]
df = df[df['rating_numerator'] < 27]
print("Dropped ", rows-df.shape[0], ' after removing wrong computed ratings.')
The maximum value that was given as rating:
In [53]:
df['rating_numerator'].max()
Out[53]:
The amount of tweets (dogs) that received the maximum rating:
In [137]:
df_best_rated = df[df['rating_numerator'] == df['rating_numerator'].max()]
df_best_rated = df_best_rated.reset_index(drop=True)
df_best_rated.shape
Out[137]:
Those are the dogs that received the maxium rating 14/10:
In [138]:
for dog in range(df_best_rated.shape[0]):
dog_url = df_best_rated['jpg_url'][dog]
print('Dog name: ', df_best_rated['name'][dog])
print('Dog breed: ', df_best_rated['p1'][dog])
print('prediction rate: ', df_best_rated['p1_conf'][dog])
print('rate: ', df_best_rated['rating_numerator'][dog], '/ 10')
display(Image(dog_url, width=400, unconfined=True))
urllib.request.urlretrieve(dog_url, "images/best_rated_dog_"+str(dog)+".jpg")
Best predicted dog breed¶
The dog breed that is predicted the easiest and its amount in the dataset:
In [61]:
df_stat = pd.DataFrame(df.groupby('p1')['p1_conf'].mean())
df_stat['count'] = df.groupby('p1')['p1_conf'].count()
In [62]:
# the 10 best predicted dog breeds
df_stat.sort_values('p1_conf',ascending=False)[:10]
Out[62]:
In [139]:
df_best_predicted = df[df['p1'] == 'komondor']
df_best_predicted = df_best_predicted.reset_index(drop=True)
Those are the dogs that belong to the best predicted breed:
In [140]:
for dog in range(df_best_predicted.shape[0]):
dog_url = df_best_predicted['jpg_url'][dog]
print('Dog name: ', df_best_predicted['name'][dog])
print('Dog breed: ', df_best_predicted['p1'][dog])
print('prediction: ', df_best_predicted['p1_conf'][dog])
print('rate: ', df_best_predicted['rating_numerator'][dog], '/ 10')
display(Image(dog_url, width=400, unconfined=True))
urllib.request.urlretrieve(dog_url, "images/best_predicted_breed_"+str(dog)+".jpg")
And here is the dog whose breed was best predicted: (also from the same breed as the best predicted breed)
In [172]:
# best predicted dog
df_best_predicted = df[df['p1_conf'] == df['p1_conf'].max()]
df_best_predicted = df_best_predicted.reset_index(drop=True)
df_best_predicted.shape
Out[172]:
In [197]:
for dog in range(df_best_predicted.shape[0]):
dog_url = df_best_predicted['jpg_url'][dog]
print('Dog name: ', df_best_predicted['name'][dog])
print('Dog breed: ', df_best_predicted['p1'][dog])
print('prediction: ', df_best_predicted['p1_conf'][dog])
print('rate: ', df_best_predicted['rating_numerator'][dog], '/ 10')
print('retweet: ', df_best_predicted['retweet_count'][dog])
print('favorite: ', df_best_predicted['favorite_count'][dog])
display(Image(dog_url, width=400, unconfined=True))
urllib.request.urlretrieve(dog_url, "images/best_predicted_breed_"+str(dog)+".jpg")
Most rated dog breed¶
The 10 most rated dog breeds:
In [65]:
df_stat.sort_values('count',ascending=False)[:10]
Out[65]:
In [141]:
df_most_rated = df[df['p1'] == 'golden_retriever']
df_most_rated = df_most_rated.reset_index(drop=True)
df_most_rated.shape
Out[141]:
Let's look at 10 dogs of the most rated breed in the dataset-the golden retriever:
In [142]:
# show only 10 of 95 dogs
for dog in range(10):
dog_url = df_most_rated['jpg_url'][dog]
print('Dog name: ', df_most_rated['name'][dog])
print('Dog breed: ', df_most_rated['p1'][dog])
print('prediction rate: ', df_most_rated['p1_conf'][dog])
print('rate: ', df_most_rated['rating_numerator'][dog], '/ 10')
display(Image(dog_url, width=400, unconfined=True))
urllib.request.urlretrieve(dog_url, "images/most_rated_breed"+str(dog)+".jpg")
most favored dog¶
The most favored dog from twitter favorite counts:
In [164]:
df_most_favorited = df[df['favorite_count'] == df['favorite_count'].max()]
df_most_favorited = df_most_favorited.reset_index(drop=True)
df_most_favorited.shape
Out[164]:
In [200]:
for dog in range(df_most_favorited.shape[0]):
dog_url = df_most_favorited['jpg_url'][dog]
print('Dog name: ', df_most_favorited['name'][dog])
print('Dog breed: ', df_most_favorited['p1'][dog])
print('favorited: ', df_most_favorited['favorite_count'][dog])
print('prediction rate: ', df_most_rated['p1_conf'][dog])
print('rate: ', df_most_favorited['rating_numerator'][dog], '/ 10')
print('retweet: ', df_most_favorited['retweet_count'][dog])
print('favorite: ', df_most_favorited['favorite_count'][dog])
display(Image(dog_url, width=400, unconfined=True))
urllib.request.urlretrieve(dog_url, "images/df_most_favorited_"+str(dog)+".jpg")
How close are the other dogs with their favorite counts? Therefore we look at the histogram of the favorite counts:
In [194]:
df['favorite_count'].hist(bins=100)
Out[194]:
most retweeted dog¶
Who is the most retweeded dog?
In [167]:
df_most_retweeted = df[df['retweet_count'] == df['retweet_count'].max()]
df_most_retweeted = df_most_retweeted.reset_index(drop=True)
df_most_retweeted.shape
Out[167]:
In [168]:
for dog in range(df_most_retweeted.shape[0]):
dog_url = df_most_retweeted['jpg_url'][dog]
print('Dog name: ', df_most_retweeted['name'][dog])
print('Dog breed: ', df_most_retweeted['p1'][dog])
print('favorited: ', df_most_retweeted['favorite_count'][dog])
print('rate: ', df_most_retweeted['rating_numerator'][dog], '/ 10')
display(Image(dog_url, width=400, unconfined=True))
urllib.request.urlretrieve(dog_url, "images/df_most_retweeted_"+str(dog)+".jpg")
Visualization¶
In [241]:
sns.set_style("whitegrid")
sns.set(rc={'figure.figsize':(20,12)})
sns.set(font_scale=2.0)
sns.set_palette("pastel")
Used dog vocabularies¶
In [242]:
df_words = df[['puppo', 'doggo', 'floofer', 'pupper']]
df_words.apply(pd.value_counts).plot(kind='bar',
title='all types')
fig = ax.get_figure()
fig.savefig("plots/dog_vocabularies.png")
Ratings over time (colored by retweet count)¶
In [243]:
df['timestamp'] = pd.to_datetime(df['timestamp'])
In [244]:
ax = sns.scatterplot(x="timestamp", y="rating_numerator",
hue="retweet_count", size="retweet_count",alpha=.8,
data=df)
ax.set_xlim(df['timestamp'].min(), df['timestamp'].max())
fig = ax.get_figure()
fig.savefig("plots/retweet_rating_over_time_b.png")
The ratings have changed over time. The ratings have increased.
Ratings over time (colored by favorite count)¶
In [245]:
ax = sns.scatterplot(x="timestamp", y="rating_numerator",
hue="favorite_count", size="favorite_count",alpha=.8,
data=df)
ax.set_xlim(df['timestamp'].min(), df['timestamp'].max())
fig = ax.get_figure()
fig.savefig("plots/favorit_rating_over_time_b.png")
If we look at the favorite count and the retweet count in the diagrams above, then we can see, that they might be similar.
Retweet and Favorite correlation¶
In [246]:
ax = sns.scatterplot(x="favorite_count", y="retweet_count",hue="rating_numerator",
alpha=.8,
data=df)
fig = ax.get_figure()
fig.savefig("plots/retweet_favorit_scatter_b.png")
The retweet and favorite count correlate.
Retweet count over Rating (colored by favorite count)¶
In [247]:
ax = sns.scatterplot(x="rating_numerator", y="retweet_count",hue="favorite_count",
alpha=.8,
data=df)
fig = ax.get_figure()
fig.savefig("plots/retweet_rating_scatter_b.png")
The low rating values are not retweeted well. The higher rating values (x-axis) are retweeted more often (y-axis). And also favored more often (color).
In [235]:
ax = sns.scatterplot(x="favorite_count", y="p1_conf",
alpha=.8,
data=df)
fig = ax.get_figure()
fig.savefig("plots/favorite_p1conf_scatter.png")
There is no correlatin between the prediction confidence of the neural network and the favorite count.